package simpledb;

import java.sql.*;
import simpledb.remote.SimpleDriver;

public class CopyOfStudentMajor {
	public static void main(String[] args) {
		Connection conn = null;
		long time1, time2;
		String oStr = "";
		int cnt =0;
		try {
			// Step 1: connect to database server
			Driver d = new SimpleDriver();
			conn = d.connect("jdbc:simpledb://localhost", null);
			ResultSet rs;
			String qry;

			// Step 2: execute the query
			Statement stmt = conn.createStatement();

			// ************* Dummy query ***********************
			qry = "opt (select SName, DName "
					+ "from ( select Did, DName " +
					"from DEPT " +
					"where DNAME = 'math' ) , STUDENT "
					+ "where MajorId = DId )";
			rs = stmt.executeQuery(qry);
			while (rs.next()) { }
			time2 = System.currentTimeMillis();
			rs.close();

			// ************* Query #1 ***********************
			qry = "opt (select SName, DName "
					+ "from ( select Did, DName " +
					"from DEPT " +
					"where DNAME = 'math' ) , STUDENT "
					+ "where MajorId = DId )";
			time1 = System.currentTimeMillis();
			rs = stmt.executeQuery(qry);
			System.out.println("Name\tMajor");
			cnt = 0;
			while (rs.next()) {
				cnt++;
				//String sname = rs.getString("SName");
				//String dname = rs.getString("DName");
				//System.out.println(sname + "\t" + dname);
			}
			time2 = System.currentTimeMillis();
			oStr = "Optomized query: " + (time2-time1) + "ms. Tuple cnt: " + cnt ;  
			System.out.println(oStr);
			rs.close();

			// ************* Query #1b ***********************
			qry = "select SName, DName "
					+ "from ( select Did, DName " +
					"from DEPT " +
					"where DNAME = 'math' ) , STUDENT "
					+ "where MajorId = DId";
			time1 = System.currentTimeMillis();
			rs = stmt.executeQuery(qry);
			System.out.println("Name\tMajor");
			cnt = 0;
			while (rs.next()) {
				cnt++;
				//String sname = rs.getString("SName");
				//String dname = rs.getString("DName");
				//System.out.println(sname + "\t" + dname);
			}
			time2 = System.currentTimeMillis();
			oStr = "Unoptomized query: " + (time2-time1) + "ms. Tuple cnt: " + cnt ;  
			System.out.println(oStr);
			rs.close();			

			// ************* Query #2 ***********************			
			qry = "opt (select SName, DName "
					+ "from DEPT, STUDENT "
					+ "where MajorId = DId and DNAME = 'math')";
			time1 = System.currentTimeMillis();
			rs = stmt.executeQuery(qry);
			System.out.println("Name\tMajor");
			cnt = 0;
			while (rs.next()) {
				cnt++;
				//String sname = rs.getString("SName");
				//String dname = rs.getString("DName");
				//System.out.println(sname + "\t" + dname);
			}
			time2 = System.currentTimeMillis();
			oStr = "Optomized query: " + (time2-time1) + "ms. Tuple cnt: " + cnt ;  
			System.out.println(oStr);
			rs.close();

			// ************* Query #3 ***********************
			qry = "select SName, DName "
					+ "from DEPT, STUDENT "
					+ "where MajorId = DId and DNAME = 'math'";
			time1 = System.currentTimeMillis();
			rs = stmt.executeQuery(qry);
			System.out.println("Name\tMajor");
			cnt = 0;
			while (rs.next()) {
				cnt++;
				//String sname = rs.getString("SName");
				//String dname = rs.getString("DName");
				//System.out.println(sname + "\t" + dname);
			}
			time2 = System.currentTimeMillis();
			oStr = "Unoptomized query: " + (time2-time1) + "ms. Tuple cnt: " + cnt ;  
			System.out.println(oStr);
			rs.close();

			// ************* Query #4 ***********************
			qry = "opt (select SName, DName "
					+ "from STUDENT, DEPT "
					+ "where MajorID = DId and MajorId = (select DID from DEPT where DNAME = 'math' ) )";
			time1 = System.currentTimeMillis();
			rs = stmt.executeQuery(qry);
			System.out.println("Name\tMajor");
			cnt = 0;
			while (rs.next()) {
				cnt++;
				//String sname = rs.getString("SName");
				//String dname = rs.getString("DName");
				//System.out.println(sname + "\t" + dname);
			}
			time2 = System.currentTimeMillis();
			oStr = "Optomized query: " + (time2-time1) + "ms. Tuple cnt: " + cnt ;  
			System.out.println(oStr);
			rs.close();

			// ************* Query #5 ***********************
			qry = "select SName, DName "
					+ "from DEPT, STUDENT "
					+ "where MajorId = DId and MajorID = (select DID from DEPT where DNAME = 'math' ) ";
			time1 = System.currentTimeMillis();
			rs = stmt.executeQuery(qry);
			System.out.println("Name\tMajor");
			cnt = 0;
			while (rs.next()) {
				cnt++;
				//String sname = rs.getString("SName");
				//String dname = rs.getString("DName");
				//System.out.println(sname + "\t" + dname);
			}
			time2 = System.currentTimeMillis();
			oStr = "Unoptomized query: " + (time2-time1) + "ms. Tuple cnt: " + cnt ;  
			System.out.println(oStr);
			rs.close();

		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			// Step 4: close the connection
			try {
				if (conn != null)
					conn.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
